package dao;

import entity.Fruit;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class FruitDao extends BaseDao{
    Connection conn = null;//定义获取连接的变量
    PreparedStatement pstmt = null;
    ResultSet rs =null;

    //查询所有
    public List<Fruit> list(int pageIndex,int pageSize) {
        List<Fruit> list = new ArrayList<Fruit>();

        //获取连接
        conn = super.getConnection();
        //sql语句
        String sql ="select * from fruit limit ?,?";

        try {
            //获取执行对象
            pstmt = conn.prepareStatement(sql);
            //设置参数
            pstmt.setInt(1,pageIndex);
            pstmt.setInt(2,pageSize);
            //执行命令
            rs = pstmt.executeQuery();
            //处理结果
            while (rs.next()) {
                Fruit f = new Fruit();
                f.setFruitId(rs.getInt("fruitId"));
                f.setType(rs.getString("type"));
                f.setBreed(rs.getString("breed"));
                f.setArea(rs.getString("area"));
                f.setBrief(rs.getString("brief"));
                f.setWeight(rs.getInt("weight"));
                f.setPrice(rs.getFloat("price"));
                list.add(f);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            super.closeAll(conn, pstmt, rs);
        }
        return list;
    }
    //根据ID查询详情
    public Fruit selectId(int id) {
        Fruit fruit = new Fruit();
        //获取连接
        conn = super.getConnection();
        //sql语句
        String sql ="select * from fruit where fruitid=?";

        try {
            //获取执行对象
            pstmt = conn.prepareStatement(sql);
            //设置参数
            pstmt.setInt(1, id);
            //执行命令
            rs = pstmt.executeQuery();
            //处理结果
            while (rs.next()) {
                fruit.setFruitId(rs.getInt("fruitId"));
                fruit.setType(rs.getString("type"));
                fruit.setBreed(rs.getString("breed"));
                fruit.setArea(rs.getString("area"));
                fruit.setBrief(rs.getString("brief"));
                fruit.setWeight(rs.getInt("weight"));
                fruit.setPrice(rs.getFloat("price"));

            }
        } catch (SQLException e) {

            e.printStackTrace();
        } finally {
            super.closeAll(conn, pstmt, rs);
        }
        return fruit;
    }
    //删除方法
    public int  del(int fruitId) {
        int result=0;
        //获取连接
        conn = super.getConnection();
        //sql语句
        String sql ="delete from fruit where fruitid=?";

        try {
            //获取执行对象
            pstmt = conn.prepareStatement(sql);
            //设置参数
            pstmt.setInt(1, fruitId);
            //执行命令
            result = pstmt.executeUpdate();

        } catch (SQLException e) {

            e.printStackTrace();
        } finally {
            super.closeAll(conn, pstmt, null);
        }
        return result;
    }
    public int count(){
        int count = 0;
        try {
            //获取连接
            conn = super.getConnection();
            //SQL语句
            String sql = "SELECT COUNT(*) FROM fruit";
            //获取执行对象
            pstmt = conn.prepareStatement(sql);
            //执行命令
            rs = pstmt.executeQuery();
            //处理结果
            while (rs.next()){
                count = rs.getInt(1);
            }
        }catch (Exception e){
            e.printStackTrace();
        }finally {

        }
        return count;
    }
    //添加方法
    public int add(Fruit fruit){
        int result = 0;
        try {
            //建立连接
            conn = super.getConnection();
            //SQL语句
            String sql = "INSERT INTO `fruit` (`type`, `breed`, `area`, `brief`, `weight`, `price`) VALUES (?,?,?,?,?,?)";
            //获取执行对象
            pstmt = conn.prepareStatement(sql);
            //设置参数
            pstmt.setString(1,fruit.getType());
            pstmt.setString(2,fruit.getBreed());
            pstmt.setString(3,fruit.getArea());
            pstmt.setString(4,fruit.getBrief());
            pstmt.setInt(5,fruit.getWeight());
            pstmt.setFloat(6,fruit.getPrice());
            //执行命令
            result = pstmt.executeUpdate();

            }catch (Exception e){
                e.printStackTrace();
            }finally {
                super.closeAll(conn,pstmt,null);
            }
            return result;
    }
    //修改方法
    public int updata(Fruit fruit){
        int result = 0;
        try {
            //建立连接
            conn = super.getConnection();
            //SQL语句
            String sql = "UPDATE `fruit` SET `type`=?, `breed`=?, `area`=?,`weight`=? WHERE `fruitId`= ?";
            //获取执行对象
            pstmt = conn.prepareStatement(sql);
            //设置参数
            pstmt.setString(1,fruit.getType());
            pstmt.setString(2,fruit.getBreed());
            pstmt.setString(3,fruit.getArea());
            pstmt.setInt(4,fruit.getWeight());
            pstmt.setInt(5,fruit.getFruitId());
            //执行命令
            result = pstmt.executeUpdate();
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            super.closeAll(conn,pstmt,null);
        }
        return result;
    }
}
